{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "file = \"/mnt/mydisk/pku_data/industry_csv/武汉.csv\"\n",
    "df = pd.read_csv(file, usecols=['注册资本', '实缴资本'], nrows=1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 获取所有币种类"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "遍历所有的csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "去除括号: \"(（）)\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "找出所有的币种\n",
    "```python\n",
    "bi_set = set()\n",
    "\n",
    "def add_bi_set(item):\n",
    "    if not isinstance(item, str):\n",
    "        bi_set.add(item)\n",
    "        return item\n",
    "            \n",
    "    if len(item) < 4:\n",
    "        bi_set.add(item)\n",
    "        return item\n",
    "    \n",
    "    try:\n",
    "        idx = item.index(\"万\")\n",
    "        if idx == -1:\n",
    "            bi_set.add(item)\n",
    "            return item\n",
    "        \n",
    "        money =item[:idx]\n",
    "        bi = item[idx + 1:]\n",
    "        bi_set.add(bi)\n",
    "        return money\n",
    "        # assert bi in exchange_rates.keys()\n",
    "        # money = float(money) * exchange_rates[bi]\n",
    "    except Exception as e:\n",
    "        # print(e, s)\n",
    "        pass\n",
    "        \n",
    "    return None\n",
    "\n",
    "folder = \"/mnt/mydisk/pku_data/industry_csv\"\n",
    "\n",
    "for idx, file in enumerate(os.listdir(folder)):\n",
    "    print(idx, file)\n",
    "    file_name = os.path.join(folder, file)\n",
    "    df = pd.read_csv(file_name, usecols=['注册资本', '实缴资本'])\n",
    "    df['注册资本'].apply(add_bi_set)\n",
    "    df['实缴资本'].apply(add_bi_set)\n",
    "def func(item):\n",
    "    if not isinstance(item, str) or len(item) == 0:\n",
    "        return False\n",
    "    return not item[0].isdigit()\n",
    "tmp = filter(func, list(bi_set))\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "RATIO = {\n",
    "    # \"塞浦路斯镑\": 6.5,\n",
    "    \"瑞士法郎\": 8.17,\n",
    "    \"马来西亚林吉特\": 1.62,\n",
    "    \"马来西亚币\": 1.62,\n",
    "    \"新西兰元\": 4.26,\n",
    "    \"智利比索\": 0.00737,\n",
    "    # \"奥地利先令\": 0.06,\n",
    "    \"法国法郎\": 0.06,\n",
    "    \"阿富汗尼\": 0.1069,\n",
    "    # \"列克\": 0.02,\n",
    "    \"英镑\": 9.22,\n",
    "    # \"德拉克马\": 0.025,\n",
    "    \"菲律宾比索\": 0.1217,\n",
    "    \"科摩罗法郎\": 0.0156,\n",
    "    \"港币\": 0.922,\n",
    "    \"澳门元\": 0.895,\n",
    "    # \"克郎\": 0.072,\n",
    "    \"人民币\": 1,\n",
    "    \"美元\": 7.17,\n",
    "    # \"加元\": 5.2,\n",
    "    \"澳大利亚元\": 4.70,\n",
    "    # \"卢布\": 0.1,\n",
    "    \"欧元\": 7.68,\n",
    "    \"新加坡元\": 5.37,\n",
    "    # \"卢比\": 0.009,\n",
    "    # \"克朗\": 0.072,\n",
    "    # \"意大利里拉\": 0.0005,\n",
    "    \"日元\": 0.046,\n",
    "    \"加拿大元\": 5.151,\n",
    "    \"港元\": 0.92,\n",
    "    \"元）\": 1,\n",
    "    \"瑞典克朗\": 0.6579,\n",
    "    # \"新台湾元\": 0.22,\n",
    "    \"哥伦比亚比索\": 0.0017,\n",
    "    \"瑞典克郎\": 0.65,\n",
    "    \"丹麦克朗\": 1.03,\n",
    "    # \"德国马克\": 4.1,\n",
    "    \"泰国铢\": 0.21,\n",
    "    \"泰铢\": 0.21,\n",
    "    \"元人民币\": 1,\n",
    "    \"韩元\": 0.005,\n",
    "    \"新台币\": 0.222,\n",
    "    \"人民币元\": 1,\n",
    "    \"元\": 1,\n",
    "    # \"比利时法郎\": 0.17,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def trans_money_ratio(item):\n",
    "    if not isinstance(item, str):\n",
    "        return 0\n",
    "    \n",
    "    import re\n",
    "    item = re.sub(r\"[()（）]\", \"\", item)\n",
    "    item = item.strip()\n",
    "\n",
    "    if len(item) <= 2:\n",
    "        return 0\n",
    "\n",
    "    try:\n",
    "        idx = item.index(\"万\")\n",
    "        if idx == -1:\n",
    "            return 0\n",
    "\n",
    "        money = item[:idx]\n",
    "        # 跳过 万 字\n",
    "        bi = item[idx + 1 :]\n",
    "        if bi in RATIO.keys():\n",
    "            money = float(money) * RATIO[bi]\n",
    "            return money\n",
    "        else:\n",
    "            return 0\n",
    "        \n",
    "    except Exception as e:\n",
    "        # print(e, s)\n",
    "        return 0\n",
    "\n",
    "    return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"new_注册资本_万\"] = df['注册资本'].apply(trans_money_ratio)\n",
    "df[\"new_实缴资本_万\"] = df['实缴资本'].apply(trans_money_ratio)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>注册资本</th>\n",
       "      <th>实缴资本</th>\n",
       "      <th>new_注册资本</th>\n",
       "      <th>new_注册资本_万</th>\n",
       "      <th>new_实缴资本_万</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>60万人民币</td>\n",
       "      <td>52万人民币</td>\n",
       "      <td>60.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50万人民币</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>400万人民币</td>\n",
       "      <td>400万人民币</td>\n",
       "      <td>400.0</td>\n",
       "      <td>400.0</td>\n",
       "      <td>400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101万人民币</td>\n",
       "      <td>101万人民币</td>\n",
       "      <td>101.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101万人民币</td>\n",
       "      <td>NaN</td>\n",
       "      <td>101.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      注册资本     实缴资本  new_注册资本  new_注册资本_万  new_实缴资本_万\n",
       "0   60万人民币   52万人民币      60.0        60.0        52.0\n",
       "1   50万人民币      NaN      50.0        50.0         0.0\n",
       "2  400万人民币  400万人民币     400.0       400.0       400.0\n",
       "3  101万人民币  101万人民币     101.0       101.0       101.0\n",
       "4  101万人民币      NaN     101.0       101.0         0.0"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = df.apply(lambda row: trans_money(row, 'currency_column'), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "llm",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
